/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseLayer;

import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author Rigsby Chi
 */
public class OfficerUpdate {
    public static void main(String arg[]){
        OfficerUpdate.approiveRefund("52584035+2");
    }
    
    public static void approiveRefund(String buyRecordID){
        
        try {
            
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            PreparedStatement pstmt = con.prepareStatement("SELECT loginName, moneyused, pointused FROM BuyRecord WHERE RECORDID=?");
            pstmt.setString(1, buyRecordID);
            ResultSet rs = pstmt.executeQuery();
            
            int pointsRefund = 0;
            String loginName = "";
            
            if(rs!=null && rs.next()){
                pointsRefund += rs.getInt("moneyused");
                loginName = rs.getString("loginname");
                
                pstmt = con.prepareStatement("UPDATE BuyRecord SET RECORDSTATE=4 WHERE recordid=?");
                pstmt.setString(1, buyRecordID);
                
                pstmt.executeUpdate();
                
                pstmt = con.prepareStatement("UPDATE LoginUser SET POINTS=POINTS+? WHERE loginname=?");
                pstmt.setInt(1, pointsRefund);
                pstmt.setString(2, loginName);
                
                pstmt.executeUpdate();
            }
            if (rs != null){
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
}
